<?php


namespace home\Controller;


class ExcelController extends ApibaseController
{
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * 基础信息列表 导出数据
     */
    public function getDruglistData()
    {
        set_time_limit(0);
        $tableHeader = ['序号', '药品编号', '种类', '名称', '规格型号', '单位', '创建日期'];
        $Druglist    = M('Druglist');
        if (trim(I('get.drug_no'))) {
            $Druglist = $Druglist->where(['drug_no' => trim(I('get.drug_no'))]);
        }
        if (trim(I('get.drug_name'))) {
            $Druglist = $Druglist->where(['drug_name' => trim(I('get.drug_name'))]);
        }
        $dataMsg = $Druglist->where('add_at > 0')->field(['id', 'drug_no', 'type', 'drug_name', 'drug_size', 'drug_unit', 'add_at'])->order(['id' => 'desc'])->select();
        if ($dataMsg) {
            foreach ($dataMsg as $k => $v) {
                $data[] = ['0' => $v['id'], '1' => $v['drug_no'], '2' => $v['type'], '3' => $v['drug_name'], '4' => $v['drug_size'], '5' => $v['drug_unit'], '6' => date('Y-m-d', $v['add_at'])];
            }
        }
        if (!$data) {
            $this->showtips('没有数据');
        } else {
            exportExcel($tableHeader, $data);
        }
    }

    /**
     * 月度仓库盘点 导出数据
     */
    public function getDataByMonth()
    {
        set_time_limit(0);
        $nowMonth    = ltrim(date('m'), '0');
        $nowYear     = date('Y');
        $year        = I('get.year', $nowYear);
        $month       = I('get.month', $nowMonth);
        $tableHeader = ['药品编号', '种类', '名称', '规格型号', '单位', '本月入库量', '本月实际用量', '库存余量', '所属仓库', '备注'];

        $model     = M('Stock s')->join('ymr_instock i ON s.drug_id = i.drug_id AND s.warehouse = i.warehouse')->where(['i.delete_at' => 0]);
        $warehouse = M('Warehouse')->select();
        foreach ($warehouse as $item => $vo) {
            $new_warehouse[ $vo['warehouse_id'] ] = $vo['warehouse_name'];
        }
        if (I('get.warehouse')) {
            $model = $model->where(['s.warehouse' => trim(I('get.warehouse'))]);
        }
        if (I('get.type')) {
            $model = $model->where(['s.type' => trim(I('get.type'))]);
        }
        if ($year && $month) {
            $stime = $year . '-' . $month;
            if ($month == '12') {
                $etime = ($year + 1) . '-1';
            } else {
                $etime = $year . '-' . ($month + 1);
            }
            $model = $model->where(['i.add_at' => ['between', [strtotime($stime), strtotime($etime)]]]);
        }
        $model   = $model->group('i.drug_id,i.warehouse');
        $getData = $model->field('s.id,s.drug_id,s.drug_no,s.drug_name,s.stock_num,s.remark,s.type,s.warehouse,SUM(i.in_stock) as month_in_num')->select();
        if ($getData) {
            foreach ($getData as $k => $v) {
                if (S('drug_info_' . $v['drug_id'])) {
                    $drug_info = S('drug_info_' . $v['drug_id']);
                } else {
                    $drug_info = M('Druglist')->where(['id' => $v['drug_id']])->find();
                    S('drug_info_' . $v['drug_id'], $drug_info, 3600);
                }
                $relause_num = M('Usedetail')->where(['drug_id' => $v['drug_id'], 'warehouse' => $v['warehouse']])->getField('sum(realuse_num) as relause_num') ? : 0;
                $data[]      = ['0' => $v['drug_no'], '1' => $v['type'], '2' => $v['drug_name'], '3' => $drug_info['drug_size'], '4' => $drug_info['drug_unit'], '5' => $v['month_in_num'], '6' => $relause_num, '7' => $v['stock_num'], '8' => $new_warehouse[ $v['warehouse'] ], '9' => $v['remark']];
            }
        }
        if (!$data) {
            $this->showtips('没有数据');
        } else {
            exportExcel($tableHeader, $data);
        }
    }

    /**
     * 物品领用统计 导出报表
     */
    public function exportDrugUsedData()
    {
        set_time_limit(0);
        $tableHeader = ['药品编号', '种类', '名称', '所属仓库', '规格型号', '单位', '领出量', '实际用量', '剩余量', '报废量', '领用课别', '组会地点', '组会医生', '组会日期', '领用时间'];
        $stime       = I('get.stime');
        $etime       = I('get.etime');
        $warehouse   = M('Warehouse')->select();
        foreach ($warehouse as $item => $vo) {
            $new_warehouse[ $vo['warehouse_id'] ] = $vo['warehouse_name'];
        }
//        $model       = M('Usedetail u')->join('ymr_drugtake d ON u.take_id = d.id')->where(['u.delete_at' => 0]);
        $model = M('Usedetail u')->join('ymr_drugtake d ON u.take_orderid = d.order_id')->where(['u.delete_at' => 0, 'u.is_outin' => 0])->field('u.*,d.order_id,d.use_class,d.group_place,d.group_doctor,d.group_date');
        if (trim(I('get.warehouse'))) {
            $model = $model->where(['u.warehouse' => trim(I('get.warehouse'))]);
        }
        if (trim(I('get.type'))) {
            $model = $model->where(['u.take_type' => trim(I('get.type'))]);
        }
        if (trim(I('get.group_place'))) {
            $model = $model->where(['d.group_place' => trim(I('get.group_place'))]);
        }
        if ($stime && !$etime) {
            $model = $model->where(['u.add_at' => ['gt', strtotime($stime)]]);
        } elseif (!$stime && $etime) {
            $model = $model->where(['u.add_at' => ['lt', strtotime($etime)]]);
        } elseif ($stime && $etime) {
            $model = $model->where(['u.add_at' => ['between', [strtotime($stime), strtotime($etime)]]]);
        }

        $getData = $model->select();
        if ($getData) {
            foreach ($getData as $k => $v) {
                if (S('drug_info_' . $v['drug_id'])) {
                    $drug_info = S('drug_info_' . $v['drug_id']);
                } else {
                    $drug_info = M('Druglist')->where(['id' => $v['drug_id']])->find();
                    S('drug_info_' . $v['drug_id'], $drug_info, 3600);
                }
                $data[] = ['0' => $drug_info['drug_no'], '1' => $drug_info['type'], '2' => $drug_info['drug_name'], '3' => $new_warehouse[ $v['warehouse'] ], '4' => $drug_info['drug_unit'], '5' => $drug_info['drug_size'], '6' => $v['take_num'], '7' => $v['realuse_num'], '8' => $v['last_num'], '9' => $v['waste_num'], '10' => $v['use_class'], '11' => $v['group_place'], '12' => $v['group_doctor'], '13' => $v['group_date'], '14' => date('Y-m-d', $v['add_at'])];
            }
        }
        if (!$data) {
            $this->showtips('没有数据');
        } else {
            exportExcel($tableHeader, $data);
        }
    }

    /**
     * 耗材领用统计 导出报表
     */
    public function exportSuppliesUsedData()
    {
        set_time_limit(0);
        $tableHeader = ['药品编号', '种类', '名称', '所属仓库', '规格型号', '单位', '领出量','领取地点','领取人', '领用时间'];
        $stime       = I('get.stime');
        $etime       = I('get.etime');
        $warehouse   = M('Warehouse')->select();
        foreach ($warehouse as $item => $vo) {
            $new_warehouse[ $vo['warehouse_id'] ] = $vo['warehouse_name'];
        }
//        $model       = M('Usedetail u')->join('ymr_drugtake d ON u.take_id = d.id')->where(['u.delete_at' => 0]);
        $model = M('Usedetail u')->join('ymr_material_take d ON u.take_orderid = d.order_id')->where(['u.delete_at' => 0, 'u.is_outin' => 0])->field('u.*,d.order_id,d.take_place,d.take_users,d.take_date');
        if (trim(I('get.warehouse'))) {
            $model = $model->where(['u.warehouse' => trim(I('get.warehouse'))]);
        }
        if (trim(I('get.type'))) {
            $model = $model->where(['u.take_type' => trim(I('get.type'))]);
        }
        if (trim(I('get.group_place'))) {
            $model = $model->where(['d.group_place' => trim(I('get.group_place'))]);
        }
        if ($stime && !$etime) {
            $model = $model->where(['d.take_date' => ['gt', $stime]]);
        } elseif (!$stime && $etime) {
            $model = $model->where(['d.take_date' => ['lt', $etime]]);
        } elseif ($stime && $etime) {
            $model = $model->where(['d.take_date' => ['between', [$stime, $etime]]]);
        }

        $getData = $model->select();
        if ($getData) {
            foreach ($getData as $k => $v) {
                if (S('drug_info_' . $v['drug_id'])) {
                    $drug_info = S('drug_info_' . $v['drug_id']);
                } else {
                    $drug_info = M('Druglist')->where(['id' => $v['drug_id']])->find();
                    S('drug_info_' . $v['drug_id'], $drug_info, 3600);
                }
                $data[] = ['0' => $drug_info['drug_no'], '1' => $drug_info['type'], '2' => $drug_info['drug_name'], '3' => $new_warehouse[ $v['warehouse'] ], '4' => $drug_info['drug_size'], '5' => $drug_info['drug_unit'], '6' => $v['take_num'], '7' => $v['take_place'], '8' => $v['take_users'], '9' => $v['take_date']];
            }
        }
        if (!$data) {
            $this->showtips('没有数据');
        } else {
            exportExcel($tableHeader, $data);
        }
    }

    /**
     *基础信息列表 导入数据
     */
    public function loadExcel()
    {
        if ($_FILES) {
            //定义允许上传的文件扩展名
            $ext_arr   = ['xls', 'xlsx',];
            $file_name = $_FILES['file']['name'];
            //获得文件扩展名
            $temp_arr = explode(".", $file_name);
            $file_ext = array_pop($temp_arr);
            $file_ext = trim($file_ext);
            $file_ext = strtolower($file_ext);
            if (!in_array($file_ext, $ext_arr)) {
                dataFormat('', 2, '文件类型错误');
            }
            if ($_FILES['file']['error']) {
                switch ($_FILES['file']['error']) {
                    case '1':
                        $error = '超过php.ini允许的大小。';
                        break;
                    case '2':
                        $error = '超过表单允许的大小。';
                        break;
                    case '3':
                        $error = '图片只有部分被上传。';
                        break;
                    case '4':
                        $error = '请选择图片。';
                        break;
                    case '6':
                        $error = '找不到临时目录。';
                        break;
                    case '7':
                        $error = '写文件到硬盘出错。';
                        break;
                    case '8':
                        $error = 'File upload stopped by extension。';
                        break;
                    default:
                        $error = '未知错误。';
                }
                dataFormat('', 1, $error);
            }
            $url = $_FILES['file']['tmp_name'];
            $res = importExcel($url);
            if ($res) {
                foreach ($res as $k => $v) {
                    //药品编号没填写的行 默认空行  删除
                    if (empty($v[0])) {
                        unset($res[ $k ]);
                        continue;
                    }
                }
                $count = count($res);
                if ($count == 0) {
                    dataFormat('', 4, '没有数据，请导入正确的表格');
                }
                $succ     = 0;
                $Druglist = M('Druglist');
                $Druglist->startTrans();
                $kinds = M('ConfigSet')->getFieldByConfigColumn('drugType', 'config_val');
                $kinds = explode(',', $kinds);
                foreach ($res as $k => $v) {
                    $exist = $Druglist->where("drug_no = '" . trim($v[0]) . "' or drug_name = '" . trim($v[2]) . "'")->find();
                    if ($exist) {
                        $Druglist->rollback();
                        dataFormat('', 6, "药品编号:$v[0]或名称:$v[2]已存在，导入失败");
                    }
                    if (!in_array(trim($v[1]), $kinds)) {
                        $Druglist->rollback();
                        dataFormat('', 7, "药品编号:$v[0]的类型$v[1]不存在，导入失败");
                    }
                    $add = $Druglist->lock(true)->add(['drug_no' => trim($v[0]), 'type' => trim($v[1]), 'drug_name' => trim($v[2]), 'drug_size' => trim($v[3]), 'drug_unit' => trim($v[4]), 'add_at' => strtotime(trim($v[5])), 'update_at' => time()]);
                    if ($add) {
                        $succ += 1;
                    }
                }
                if ($succ == $count) {
                    $Druglist->commit();
                    dataFormat('', 0, '导入成功');
                } else {
                    $Druglist->rollback();
                    dataFormat('', 5, '导入失败[1]');
                }
            } else {
                dataFormat('', 3, '导入失败[0]');
            }
        }
    }

    /**
     * 库存查询 导出表格
     */
    public function getStockData()
    {
        set_time_limit(0);
        $tableHeader = ['序号', '药品编号', '种类', '名称', '规格型号', '单位', '库存数量', '所属仓库'];

        $Stock     = M('Stock');
        $warehouse = M('Warehouse')->select();
        foreach ($warehouse as $item => $vo) {
            $new_warehouse[ $vo['warehouse_id'] ] = $vo['warehouse_name'];
        }
        if (trim(I('get.drug_no'))) {
            $Druglist = $Stock->where(['drug_no' => trim(I('get.drug_no'))]);
        }
        if (trim(I('get.drug_name'))) {
            $Druglist = $Stock->where(['drug_name' => trim(I('get.drug_name'))]);
        }
        $dataMsg = $Stock->field(['id', 'drug_id', 'drug_no', 'drug_name', 'stock_num', 'warehouse'])->select();
        if ($dataMsg) {
            $drug_kinds = $Stock->field('drug_id')->group('drug_id')->select();
            $drug_info  = [];
            foreach ($drug_kinds as $key => $val) {
                if (S('drug_info_' . $val['drug_id'])) {
                    $drug = S('drug_info_' . $val['drug_id']);
                } else {
                    $drug = M('Druglist')->where(['id' => $val['drug_id']])->find();
                    S('drug_info_' . $val['drug_id'], $drug, 3600);
                }
                $drug_info[ $val['drug_id'] ] = $drug;
            }
            foreach ($dataMsg as $k => $v) {
                $data[] = ['0' => $v['id'], '1' => $v['drug_no'], '2' => $drug_info[ $v['drug_id'] ]['type'], '3' => $v['drug_name'], '4' => $drug_info[ $v['drug_id'] ]['drug_size'], '5' => $drug_info[ $v['drug_id'] ]['drug_size'], '6' => $v['stock_num'], '7' => $new_warehouse[ $v['warehouse'] ]];
            }
        }
        if (!$data) {
            $this->showtips('没有数据');
        } else {
            exportExcel($tableHeader, $data);
        }
    }

    /**
     *  耗材报备 导出表格
     */
    public function getMaterialData()
    {
        set_time_limit(0);
        $tableHeader = ['序号', '组会日期', '组会地点', '组会课别', '组会医生', '耗材报备内容', '登记人', '操作账号', '创建时间'];

        $MaterialReported = M('MaterialReported');
        if (I('get.group_date')) {
            $MaterialReported  = $MaterialReported->where(['group_date' => I('get.group_date')]);
            $url['group_date'] = I('get.group_date');
        }
        if (I('get.group_place')) {
            $MaterialReported   = $MaterialReported->where(['group_place' => I('get.group_place')]);
            $url['group_place'] = I('get.group_place');
        }
        if (I('get.reporter')) {
            $MaterialReported = $MaterialReported->where(['reporter' => I('get.reporter')]);
            $url['reporter']  = I('get.reporter');
        }
        $stime = I('get.start_date');
        $etime = I('get.end_date');
        if ($stime && !$etime) {
            $MaterialReported = $MaterialReported->where(['add_at' => ['gt', strtotime($stime)]]);
            $url['stime']     = $stime;
        } elseif (!$stime && $etime) {
            $MaterialReported = $MaterialReported->where(['add_at' => ['lt', strtotime($etime)]]);
            $url['etime']     = $etime;
        } elseif ($stime && $etime) {
            $MaterialReported = $MaterialReported->where(['add_at' => ['between', [strtotime($stime), strtotime($etime)]]]);
            $url['stime']     = $stime;
            $url['etime']     = $etime;
        }
        $dataMsg = $MaterialReported->field(['id', 'group_date', 'group_place', 'group_class', 'group_doctor', 'report_content', 'reporter', 'operator', 'add_at'])->select();
        if ($dataMsg) {
            foreach ($dataMsg as $k => $v) {
                $data[] = ['0' => $v['id'], '1' => $v['group_date'], '2' => $v['group_place'], '3' => $v['group_class'], '4' => $v['group_doctor'], '5' => $v['report_content'], '6' => $v['reporter'], '7' => $v['operator'], '8' => date('Y-m-d H:i:s', $v['add_at'])];
            }
        }
        if (!$data) {
            $this->showtips('没有数据');
        } else {
            exportExcel($tableHeader, $data);
        }
    }
}